package com.wisdom.system.common.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * Excel工具类
 */
public class ExcelUtil {

    /**
     * Excel表格导出
     *
     * @param response    HttpServletResponse对象
     * @param excelData   Excel表格的数据，封装为List<List<String>>
     * @param sheetName   sheet的名字
     * @param fileName    导出Excel的文件名
     * @param columnWidth Excel表格的宽度，建议为15
     * @throws IOException 抛IO异常
     */
    public static void exportExcel(HttpServletResponse response,
                                   List<Map<String, Object>> excelData,
                                   String sheetName,
                                   String fileName,
                                   int columnWidth,
                                   String titleName) throws IOException {

        //声明一个工作簿
        HSSFWorkbook workbook = new HSSFWorkbook();

        //生成一个表格，设置表格名称、样式
        HSSFSheet sheet = workbook.createSheet(sheetName);
        //创建Cell样式并设置样式
        HSSFCellStyle hstyle = workbook.createCellStyle();
        //水平居中
        hstyle.setAlignment(HorizontalAlignment.CENTER);
        //竖直居中
        hstyle.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置表格列宽度
        sheet.setDefaultColumnWidth(columnWidth);
        Map<String, Object> headname = excelData.get(0);
        //创建第一行
        HSSFRow row = sheet.createRow(0);
        //获取第一个单元格(第一列)
        HSSFCell cell = row.createCell(0);
        //给第一个单元格赋值
        cell.setCellStyle(hstyle);
        cell.setCellValue(new HSSFRichTextString(titleName));

        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, headname.size()));
        //在sheet里创建第二行
        HSSFRow row2 = sheet.createRow(1);
        int index = 0;
        Set<String> titles = headname.keySet();
        Iterator<String> it = titles.iterator();
        while (it.hasNext()) {
            row2.createCell(index).setCellValue(it.next());
            index++;
        }
        int index1 = 0;
        for (int i = 0; i < excelData.size(); i++) {
            HSSFRow row3 = sheet.createRow(i + 2);
            Map<String, Object> map = (Map<String, Object>) excelData.get(i);
            Iterator iterator = map.keySet().iterator();
            while (iterator.hasNext()) {
                String key = (String) iterator.next();
                Object object = map.get(key);
                //创建一个内容对象
                HSSFRichTextString text = new HSSFRichTextString(object.toString());
                //将内容对象的文字内容写入到单元格中
                row3.createCell(index1).setCellValue(text);
                index1++;

            }
            index1 = 0;
        }
        //准备将Excel的输出流通过response输出到页面下载
        //八进制输出流
        response.setContentType("application/octet-stream");

        //设置导出Excel的名称
        response.setHeader("Content-disposition", "attachment;filename="+new String(fileName.getBytes("utf-8"),"iso-8859-1") + ".xls");

        //刷新缓冲
//        response.flushBuffer();

        //workbook将Excel写入到response的输出流中，供页面下载该Excel文件
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);

        //关闭workbook
        workbook.close();
    }

}
